Contents

About Copyright, Registration, Technical Support, License

CrossTabs

Export Data

Graph

Labels (Address Labels)

Print SQL Report

Refine Query

Save, Retrieve SQL File

Select Database and Table

SQL Query

SQL Expressions and Functions

Table Links

Tagging Items

Tutorial, Getting Started


Tutorial
Tutorial, Getting Started

1.	If the correct Query grid is not already displayed, click on the Select Database button (filing cabinet) in the toolbar.  The Database Selection window is displayed.

2.	In the Database Selection window, select the type of database, the directory of the database file (DOUBLE-click)and the database file.  For MS Access database, you must also select the table.  Click on OK.  The Query grid for the selected database is displayed.

3.	We will do a simple query.  Click on a cell in the Source Expression column.  In the pop-up entry field (with the blinking insert point), type the filter expression, i.e. the word or value that you would like to find (relating to the selected field).  For example, if you have selected the State field, enter CA and the system will display all records where the State field is 'CA'.  To search for an embedded word, type an asterisk in front of the search word.  For example, *Smith will give you all the records where the field includes the word 'Smith', including John Smith, H. Smith, etc.

4.	Click on the Run button (green spot) and review the displayed report.

Run some other queries.  Use multiple search criteria.  Experiment with aggregates, column totals, sorting, wildcards, etc.

The Online Help explains the various functions of this powerful query system.


Copyright, Registration, Technical Support

This system was developed by Informatik Inc, P.O. Box 868, Devon, Pennsylvania 19333.  For information on program modifications, systems integrations and custom applications, please contact Informatik Inc.

This is a copyright-protected shareware product.  If, after review and evaluation, you like the software, please register.  Shareware is based on trust, and we hope that all users will register.  Upon registration and payment of the registration fee, Informatik Inc will send you the latest master diskette, a registration number and a User Guide.  Registered users will receive technical support and software upgrades as specified in the application.

To register

From the About menu, choose 'Registration, Copyright, License' and follow the instructions.

To obtain technical support

From the Help menu, choose Technical Support and follow the instructions.

SeekWell  and Infothek aretrademarks of Informatik Inc.

© 1994-1995 Informatik Inc

All rights reserved


Export Data

Before you export data, the data must be selected with a query.  The export will include only the records listed in the Query Report.  The export format is standard "ASCII delimited" or 'tab delimited'.

To export the records on the SQL Report to a file (Comma and Quote delimited)

1.	In the Query window, run a  query to select the records for export.

2.	Optionally, tag the records that you wish to export.  See Tagging Items

3.	From the File menu, choose Export Data.

4.	In the Save Dialog, select the file name.  You may need to change the directory and/or extension name first.

5.	Choose OK.

To export the records to the Clipboard  (Tab delimited)

1.	In SQL Query, run an SQL query to select the records for export.

2.	Highlight the cell area that you wish to export to the clipboard  (tags are ignored).

3.	From the File menu, choose Export to Clipboard.

4.	In a spreadsheet application, use the Paste function to copy the data into the spreadsheet.


Graph
Graphs

Records displayed in the SQL Report can be converted to a graph.

To create a graph

1.	Run a query.  Do NOT format any of the values.  You may want to run a crosstab.

See Crosstab

2.	Highlight the cell area that is applicable to the graph.  While holding down the left mouse button, drag the cursor over the selected records and pertinent columns.  Release the left mouse button.

The first column highlighted represents the labels for the X-axis of the graph.

3.	The second and subsequent columns of the highlighted area must be numeric data fields that represent the data sets for the graph (Y-axis).  The graph will only include numeric data columns; any non-numeric data columns are ignored.

4.	If the columns are not correctly aligned, re-sequence the columns with the 'Sequence of Columns' option in the Report menu and rerun the query.

5.	From the Report menu, choose Graph.  The Graph window appears.

6.	Select the type of graph, choose the style, add grids, titles, etc.  The graph is automatically redrawn.

7.	From the File menu, select Save or Print.  All saved files are in the Metafile (WMF) format.  You can also copy the graph to the clipboard and paste it to other Windows applications, such as a word processors.  To copy to the clipboard, choose Copy from the Edit menu.

8.	Press the close button, or choose Exit from the File menu.


Address Labels

To create a data merge file for label printing

1.	Select the customer name, address and other label fields with a query.  Ensure that the zip code column is in the logical city-state-zip order.  To change the sequence of a field, place the cursor in the field that you want to re-sequence, then choose Sequence of Columns from the Report menu.

2.	From the File menu, select Print Labels.  The Labels window opens.

3.	In the Labels window, choose a standard label format from the Labelsize menu, or choose a private format from the File menu.

To customize a new format specify the parameters:  Select the measurement standard (millimeter or inch) and select  the appropriate word-wrap, blank-line-compaction modes.

In the fields provided, type the margins, width and height of the label stock, and specify the number of columns and rows.  The margin must be a at least 1/4 inch.  The width represents the distance from the left edge of the label to the left edge of the next label.  The height represents the distance from the top edge of the label to the top edge of the next label. Postnet bar codes require a minimum of 1/8 inch left margin and a minimum of 1/25 inch top margin.

If you have previously saved the label specifications under the name 'Label.lbl', the system will use these specifications as a default.

4.	In the Merge Fields box, you can change the layout of the label.  Each number displayed in the box is a placeholder for the text of the numbered column in the SQL Report.  You can re-sequence and realign the fields, delete fields, add spacing, add punctuation signs, add literal characters, etc.  The printing function always places a single space between the fields if the field is not blank.  To force a blank line, type [CR] on the blank line.

5.	To convert a text to a bar code (Code39), type BARCODE immediately after the field flag, for example [5]BARCODE.  To convert a text o a POSTNET bar code, type the word POSTNET immediately after the field flag.

6.	From the Font menu, choose Font Selection.  Select the font name and font attributes, such as style, size, bold, etc.  Choose OK.

7.	Load your printer with the correct label paper.

8.	Choose the View/Print button, or choose Print Labels from the File menu.  A sample label of the active record is displayed.

9.	Review the sample display.  To continue, press OK.  In the Printer dialog, select the number of copies.  Choose OK.

10.	To save the print specifications, select Save As from the File menu.  Specify the directory and file name, then press the OK button.

11.	Press the Close button, or choose Exit from the File menu.


Print  SQL Report
Printing the Report

To print a report

1.	Before you print the report, you may want to  adjust the column width.  In the heading of the report, point to the column partition line and, while holding the left curser down, move the line to the left or right.

2.	From the File menu, choose Print Standard Report.  To print the field values vertically, choose Print Vertical Report.

3.	The system prompts you to enter a short report heading.

4.	In the Print Dialog, select the printer (if required).

5.	Choose OK.


Save SQL Query
Save, Retrieve SQL Query

An SQL query statement can be saved to disk and later be retrieved.  Saved queries ignore password security.  DO NOT SAVE QUERIES THAT SHOULD BE SUBJECT TO PASSWORD SECURITY!

To save an SQL query

1.	Create an SQL query.

2.	From the File menu, choose Save SQL.

3.	In the Save Dialog, select the file name.  Ensure that you are in the correct directory.

4.	Choose OK.

To open (retrieve) an SQL query

1.	From the File menu of SQL Query, choose Open SQL.

2.	In the Open Dialog, select the file name.  You may need to change the directory and/or extension name first.

3.	Choose OK.


Select Database and Table
Select the Database and Table

Before you run a query, ensure that you are accessing the correct database and table.

To change the database or the table

1.	Open the Database Selection window.

2.	In the Database Selection:

Verify the drive.  Change it, if necessary.

Choose the directory from the Directories listing (double-click).

From the Databases listing, choose the database.

For Microsoft Access databases. choose the table from the Tables listing.

Select the type of database from the listing of supported databases.  These are the databases for which you have 'Installable ISAMs' and which are listed in the .INI file in the application's directory.

If required, enter the user ID and password.  For Microsoft Access databases, enter the user name and the password (separated by a comma).  For other databases, enter the password.

3.	Choose OK.


SQL Query

1.	Open the Query window.  From the Edit menu, choose Clear Screen, or press the Clear button (Cross-out).  This step is not required if the screen is already cleared.

2.	In the SQL Query Builder grid, select the individual fields that you want listed as columns in the Query report. Just click the cell in the Select column.  A check mark appears in the selected cell.  The fields are already pre-selected.  Click the blue 'Check mark' button to include all fields, or the white 'Check mark' button to exclude all fields.  To un-select an individual field, place the cursor in the appropriate cell in the Select column and click, or press the Delete key.

The selected fields can now be filtered, aggregated (summarized) and sorted, as described below.  In addition to the listed database table fields, you can create calculated data columns, such as variances, data permutations, etc.  This more advanced but powerful application is described at the end of this topic.

3.	Click a Source Expression cell.  A listing of available Filter Operators is displayed at the top of the screen.  In most cases, you will use the '= is' or the 'LIKE' filter operator.  If so, you need not select the filter operator; the system will do it for you.  The default filter operator depends on the type of the field (numeric or non-numeric)

 4.	In the input box (below the SQL Query Builder grid), type a filter expression.  The entry can be a single value, a wild card word or an expression.  If the filter expression uses a word that is also a field name or a system keyword (but unrelated), place a single quote mark at the beginning and at the end of the word, or place an asterisk at the end of the word.

Examples:

>= 1000    (More or equal 1000)

IN NY, PA, NJ   (just items in NY, PA and NJ)

NOT IN NY,PA,NJ   (all, except in NY, PA and NJ)

BETWEEN 1000 and 2000   (items between 1000 and 2000)

NOT BETWEEN 1000 and 2000   (all, except items between 1000 and 2000)

LIKE SM   (all items starting with the letters 'SM')

< date()   (transactions before today's date)

<> TrxDate    (items that are not equal to the date in the TrxDate field)

Is NULL   (records with a blank field)

For more detail please see SQL Expressions and Functions

5.	Complete your query by selecting summary, sorting, formatting operators and column totals. These features are optional and are often not required for straightforward queries.  If you place filters in several fields, review the 'and/or' parameters.  They are defaulted to 'and' but you may wish to change them to 'or'.

6.	You can rearrange the sequence (order) in which the fields are displayed in the SQL Report.  In the SQL Builder grid , select the row that you want to re-arrange.   From the Report menu, choose Sequence of Columns and specify the new row position.

7.	Press Query button (green spot),  press F8 or choose Run Report from the Report menu.  A listing of matching records is displayed.  Adjust the column width, if necessary, by dragging the column separator in the column heading.

8.	Optionally, for interest, review the SQL statement.  From the Report menu, choose View SQL Statement.  If you know the SQL language, feel free to change the SQL statement.  To close the SQL window, run the SQL query or choose Hide SQL Statement from the Report menu.

If you modify the SQL Statement, it must be run with the Execute SQL Statement in the Report menu, NOT with the Query button.

9.	You can refine the results with additional filters and sorts.  From the Report menu, select Refine Query.  See Refine Query.

10.	To view the detail of one record tabulated in vertical format, select the record and choose Summary of Record from the Report menu (Y + CTRL) or right-click on the record.

Aggregate and Filter Combo

You can combine an aggregate function with a filter expression.  For example, you may want to list the states where the total aggregate sales volume exceeded one million dollars.  In this case insert the 'Sum' aggregate in the Sales Volume row, select the > filter operator and type 1000000 as the filter expression.  Check the State row (only the State row should be checked; all other rows should normally remain un-checked.)

Create calculated columns

You can create additional 'calculated' columns, such as variances between two fields, calculated values, etc.  Click the blank Field cell (second column) with a 'plus' picture in the bottom row.  Type the selection formula.

Examples:

Sales - Budget AS Variance

will display the variance between the Actual and Budget fields and produce a new report column labelled 'Variance':

Datepart("m",InvDate) AS Month

will display the month of the InvDate field and produce a new report column labeled Month. (For quarters, use "q", for year, use "yyyy", for day, use "d")

IIF(Actual > Budget ,"Good","Bad")

will display 'Good' if the Actual exceeds Budget, and 'Bad' if Actual is below budget.

Date() - [InvDate]

will display the number of days between a  date shown in the InvDate field and today's date.

If the field name has an embedded space, you must enclose the field name in square brackets, e.g. [Sales Amount].  Notice that the keyword 'AS' (or 'as') is required to specify the report column label.  Without the AS keyword, the column header will be a system generated code.

There is virtually no limit what you can do.  Any good book on SQL will give you further insight into the


Tagging Items

Records on the SQL Report can be tagged.  Tagging is useful if you want to print  or export only a few selected records  from a SQL Report.

To tag records

In the SQL Report, double-click the record.  A check mark appears in the first cell of the record.  To undo a tag, simply double-click the record again, or press the Delete key.

-or-

Highlight the rows and choose Tag Record from the Edit menu.  To highlight the rows, drag the mouse over the records while holding down the left mouse button.

The printing or export functions include only tagged items (if there is at least one tagged item).

Tagging has no effect on the Filter and Sort methods in the Refine menu.  The Refine procedure removes all the tags.


Crosstabs

Often, you want to see the result of a query in the form of a spreadsheet or 'crosstab'.  Crosstabs are also ideal foundations for graphs.

To create a crosstab

1.	Open the Query window.

2.	Specify the filter criteria as you would for a regular query.  Sort and aggregate functions will be ignored in the crosstab.

3.	From the Crosstab menu, select Crosstab.  The Crosstab window opens.

4.	From the Group list, select the Group field.  In the spreadsheet, the Group is the left side vertical label.

5.	From the  Product list, select the Product field. In the spreadsheet, the Product field is the horizontal spread, e.g. products.

6.	From the Data list, select the Data field.  In the spreadsheet, the Data is the two-dimensional spread of detail, e.g. the body of data.

7.	From the Aggregate (Summaries) list, select the type of 'aggregate' calculation, e.g. sum, maximum, minimum, average, item count.  "Sum' is the traditional aggregate.

8.	Optionally, select a sort type and an format for the data.

9.	Choose OK.

The crosstab function create an SQL statement which can be viewed and modified.  From the Report menu, choose View SQL.  You should not change the SQL statement unless you are familiar with the SQL language.  A modified SQL query must be run from the Report menu (Execute SQL Statement), NOT with the Query button.


SQL Expressions
SQL Expressions and Functions

See also:	 SQL Query

The SQL filter consists of a filter operator and a filter expression.  The format of the filter expression depends on the type of filter operator.  Some examples of filter operators and and filter expressions are shown below:

= 	Equal (is)

>	More

>=	More or equal

<	Less

<=	Less or equal

<>	Not equal

These operators search for exact matches.  The filter expression can be a single value, a formula or a reference to a table/field, system date function, etc

1/1/94, 3/31/94-1/31/94

100, 1000+250-(50/2)

Actual, Budget.  (Where Actual and Budget are field names)

Date(), Date() + 30.  (Date () is a function that returns the current date)

LIKE, NOT LIKE

These operators search for patterns, not exact matches.  The filter expression typically is a word (or phrase) with embedded or trailing wild card characters.  There are three types of wild card characters:

? stands for a single character,

# stands for a single digit (0-9),

* stands for a sequence of zero or more characters.

The system always appends a trailing asterisk (*) and you should not use it at the end of a word. (But you can remove it in the SQL statement. To do so, go to the SQL menu).

Examples of filter expressions:

SM returns Smith, Smyth, Smeaton, etc  (remember, the trailing asterisk is not required)

M?LLER returns Miller, Muller, etc

##/12/95 returns all the dates in December 1995

'Miller' returns only Miller names

*Miller' returns names ending with Miller, such as Joe Miller, Kornmiller, etc.

BETWEEN,  NOT BETWEEN

The filter expression must provide the 'from' and the o' values, separated by the 'and'.  Wildcard characters are not allowed.

Example:  100 and 200

IN, NOT IN

This very useful (and often overlooked) filter expression returns the records that match the listed values.  Don't forget to enter commas between the values.  Wildcards are not permitted.

Example: 100, 110,120,130,140

IS NULLIS, NOT NULL

Returns blank (or not blank) values.  This operator is useful to isolate fields that are blank or not blank.

Functions

The supported functions include:  DateSerial, Year, Month, Day, Weekday, TimeSerial, Hour, Minute, Second, Chr, Asc, Hex, Oct, Val, Fix, Int, Abs, Sgn, Atn, Cos, Sin, Tan, Format, Lcase, Ucase, Space, String, Len, Instr, Left, Mid, Right, Trim, Ltrim, Rtrim, DateDiff, DateAdd, DatePartDate, Time, Now, Iif, IsNull, IsDate, IsDate, IsNumeric.

Some of the most often used functions are explained below.

Date()

Returns the current system date.

DatePart()

Extracts part of the date, for example the month, the quarter, the year.

Example: DatePart("m",Date()) returns the month of the current date.  To extract the quarter, use "q", to extract the year, use "yyyy", to extract the day, use "d".

DateAdd()

Adds days, months or years to a date.

Example: DateAdd("m",1,Date()) adds 1 month to today's date.

DateDiff()

Returns the number of days (etc) between two specified dates

Example: DateDiff("d","3/1/95","1/31/95")

Example: DateDiff(Duedate,Billingdate)

Example: DateDiff(Date(),Billingdate)

Format()

Converts the parameter.

Examples:

Format(Date(),"mm/dd/yy")

Format(Datefield,"dddd")

Format(Datefield,"hh:mm AM/PM")

See Format section below.

Left()

Returns the specified number of characters from the left.

Example: Left(Customer,5) returns the 5 left-most characters of the Customer field.

Right()

Returns the specified number of characters from the right.

Example: Right(Customer,5)

Mid()

Returns the specified number of characters inside a string.

Example: Mid(Customer,4,5) returns 5 characters from the Customer field, starting with the 4th character.  If the 'length' parameter is blank, all characters from the starting point are returned.

Len()

Returns the number of characters in a string.

Example: Len(Customer) returns the number of characters in the Customer's name.

InStr()

Returns the character position of a searched string.

Example: InStr("Abcdefg","d") returns 4 (d is the fourth letter in the string).

IIF()

Evaluates the expression and returns the correct answer.

Example: IIF(Due < Date(),"Overdue","").  If the Due date (from the table) is earlier than today's date, show "Overdue", otherwise leave blank.

IsNull(), NOT isNull

Returns TRUE if value is NULL (i.e. invalid)

Example: IFF(IsNull(Billingdate),"INVALID","VALID").  If Billingdate field is invalid (null), return INVALID, otherwise return VALID.

Example: IFF (NOT IsNull(Billingdate),"VALID,"INVALID").  Opposite of above example.

LTrim(). Rtrim(), Trim()

Trims the blank spaces at the left, at the right or both sides.  This function is useful when concatenating fields where a field may be blank.

Example: Rtrim(Firstname & " " & Middlename) & " " & Lastname.  The extra space caused by a non-existing middle name is eliminated.

Arithmetic Operators

+, -, *, /  are the basic mathematical functions for adding, deducting, multiplying and dividing.

Use the back-slash (\) for divisions where the result must be an integer (no fractional values).  For example: 100 \3 = 33

Use Mod to find the fractional value of a division.

For example: 100 Mod 3 = .33333

Use the ampersand (&) operator to concatenate text values:.

For example: [City] & ", " & [State] & " " & [Zip] = Devon, Pa 19333

(City, State and Zip are database fields)

Format

The Format function requires specific format keywords.  Please refer to the Format function above.

Popular format keywords are:

q	Quarter of the year (14)

y	Day of the year (1366)

yy	2digit year

yyyy	4digit year

m    	Month without leading zero

mm    	Month with leading zero, e.g. 09

mmm	Abbreviated month, e.g. Feb

mmmm	Month spelled out, e.g. December

d	day without leading zero

dd	day with leading zero, e.g. 02

ddd	Abbreviated day, e.g. Wed

dddd	Day spelled out, e.g. Friday

hh	Hour

mm       	Minute

AM/PM	Uses 12hour clock, e.g. 3:15 PM

am/pm	Uses 12hour clock, e.g. 3:15 pm

A/P    	Uses 12hour clock, e.g. 3:15 P

a/p 	Uses 12hour clock, e.g. 3:15 p

Currency	Amount with currency sign, e.g. $1,000.00

Standard	Standard value presentation, e.g. 12,000.00

Percent	Expressed as a percentage, e.g. 10%

#,##0.00##	Number with minimum of two and maximum of four decimals

Long Date	Date format as specified in Windows Control Panel

Medium Date	Date format as specified in Windows Control Panel

Short Date	Date format as specified inWindows Control Panel


Refine SQL Report
Refine the SQL Query

Sometimes you may not be satisfied with the SQL Report.  Instead of re-creating the SQL statement, you can refine the current report with sorting, additional filtering, and totals.

To filter out data from an existing SQL Report.

1.	From the Report menu, choose Refine Query | Filter.  The SQL Filter window appears.

2.	From the Field list, select the field on which you want to apply a filter.

3.	From the Filter Operator list, select the filter operator.

4.	In the Filter Expression field, enter the filter expression.

5.	Choose OK.

To re-sort the existing SQL Report

1.	From the Report menu, choose Refine Query | Sort.

2.	From the list of options, select the field and sort option.


Table Links
Link Tables

The system is primarily intended for queries of one single table.  However, the system does allow for several tables within the same database to be linked and searched.

To run a search involving multiple tables

1.	In the Database Selection window, select the main table and open the Query window.

2.	In the Query window, choose the Links menu.  A list of previously saved links is displayed.  Select one of the links or press the Insert Link for a new link.

3.	If you press the Insert Link (for a new link), the Table Link Specification window is displayed.  Specify the two linking tables and the linking fields.  The field that you link to should be an indexed field.  Press OK.

You may want to save the link for future use.  To save the link, choose Save Link (Append) from the File menu.  The link specification will be appended to the LINK.TXT file.  To edit the LINK.TXT file you should use a text editor, such as Notepad.

4.	Back in the Query window you can review the links by simply pressing the Links menu.

5.	Once the tables are linked, you must specify the field that you want listed on the report.  Because the fields of secondary tables are not listed on the screen, you will need to enter them in the last cell (the cell with a cross picture.  Use the vertical scroll bar to view the bottom of the query builder.

It is important that the field names include the name of the table and the name of the field, separated by a period.  Furthermore, both the table and the field name must be enclosed in brackets.  For example,  the field Salesperson in the Sales table would be types as [Sales].[Salesperson].  If the table and field names are not reserved keywords and if they have no embedded space, then you need not use the brackets.   Also, if the table name is unique (only used in one of the tables), then the table name need not be entered.  To be safe, it is advisable to always use the table name and the brackets.

6.	To clear all links, choose Clear Links from the Links menu.  If you clear the screen, the links are also cleared.